/*****************************************************************************
 *
 *                      HUSONG PROPRIETARY INFORMATION
 *
 *          The information contained herein is proprietary to HUSONG
 *           and shall not be reproduced or disclosed in whole or in part
 *                    or used for any design or manufacture
 *              without direct written authorization from HUSONG.
 *
 *            Copyright (c) 2015 by HUSONG.  All rights reserved.
 *
 *****************************************************************************/
package me.send.broker.mapper;

import java.util.List;
import java.util.Map;

import me.send.broker.entity.Broker;
import me.send.broker.entity.Feedback;
import me.send.broker.entity.TrainPic;
import me.send.broker.entity.base.TrainTest;
import me.send.broker.pojo.BrokerBean;
import me.send.broker.pojo.BrokerIndexBean;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

/**
 * @author maxmin
 *
 * @date 2015年8月3日 下午4:08:28
 */
public abstract interface BrokerMapper {
	// 注册 2015年7月15日09:03:50
	@Insert(value = "insert into popuser (username,password,telephone,createtime,updatetime,status,openAccountId,isvAccountId,cityname,bindmac) "
			+ "values(#{username},#{password},#{telephone},#{createtime},#{updatetime},#{status},#{openAccountId},#{isvAccountId},#{cityname},#{bindmac})")
	public abstract void save(Broker popuser);

	// 检查这个手机号码是否注册过。2015年7月15日10:00:53
	@Select(value = "select count(1) as total from popuser where telephone = #{telephone}")
	public abstract int count_telephone(String telephone);
	
	// 登录
	@Select(value = "select * from popuser where telephone = #{telephone} and password = #{password}")
	@Results({
		@Result(column="istrained",property="isTrained"),
		@Result(column="istested",property="isTested"),
		@Result(column="real_name",property="realName"),
		@Result(column="isauthen",property="isAuthen"),
		@Result(column="authen_tel",property="authenTel"),
		@Result(column="positive_id_path",property="positiveIdPath"),
		@Result(column="negative_id_path",property="negativeIdPath"),
		@Result(column="hand_id_path",property="handIdPath")
	})
	public abstract Broker login(Broker popuser);

	// 修改密码 2015年7月20日09:29:37
	@Update(value = "update popuser set password = #{password},updatetime = #{updatetime} where telephone = #{telephone}")
	public abstract int update_password(Broker popuser);

	// 修改昵称 修改头像 修改性别 2015年7月20日09:44:18
	@Update(value = "update popuser set username = #{username},updatetime = #{updatetime} where popid = #{popid}")
	public abstract int update_username(Broker popuser);

	@Update(value = "update popuser set sex = #{sex},updatetime = #{updatetime} where popid = #{popid}")
	public abstract int update_sex(Broker popuser);
	
	@Select(value="SELECT count(1) FROM popuser WHERE username=#{username} AND popid<>#{popid}")
	public abstract int getIsNameRepeat(Map<String, Object> params);
	
	/**
	 * 修改用户的用户名和性别
	 * 
	 * @param popuser
	 * @return
	 */
	@Update(value = "update popuser set sex = #{sex},username = #{username},updatetime = #{updatetime} where popid = #{popid}")
	public abstract int updateInfo(Broker broker);

	@Update(value = "update popuser set headerpath = #{headerpath},updatetime = #{updatetime} where popid = #{popid}")
	public abstract int update_headerpath(Broker popuser);

	@Update(value = "update popuser set openAccountId = #{openAccountId},updatetime = #{updatetime} where popid = #{popid}")
	public abstract int update_openAccount(Broker popuser);
	
	@Select(value = "SELECT popid, username,headerpath,`status`,sex,telephone,"
			+ "(select sum(tip) as tips from orders where popid = #{brokerid} and status >=8 ) user_tip,"
			+ "(select count(1) as total from orders where popid = #{brokerid} and status >=8) order_count "
			+ "FROM popuser WHERE	popid = #{brokerid} ")
	@Results({
		@Result(column="user_tip",property="userTip"),
		@Result(column="order_count",property="orderCount")
		})
	public abstract BrokerBean getBrokerInfo(int brokerid);
	
	
	/**
	 * 经纪人信息
	 * @param brokerid
	 * @return
	 */
	@Select(value = "SELECT popid, username,headerpath,`status`,sex,telephone,isauthen,level,"
			+ "(SELECT IF(COUNT(1)>0,SUM(be.point)/COUNT(be.point),0) "
			+ "FROM broker_evaluate be LEFT JOIN orders o ON be.orderid=o.orderid "
			+ "LEFT JOIN orderbroker ob ON o.orderid=ob.order_id WHERE ob.broker_id=#{brokerid} AND ob.ischecked=1) comment_count,"
/*			+ "(SELECT count(1) comment_count FROM `comment` c WHERE c.orderid "
			+ "IN (SELECT DISTINCT o.orderid FROM orders o LEFT JOIN orderbroker ob "
			+ "ON o.orderid=ob.order_id WHERE ob.broker_id=#{brokerid} AND ob.ischecked=1)) comment_count,"
*/			
			+ "(select count(1) from orders o1 LEFT JOIN orderbroker ob1 ON o1.orderid=ob1.order_id "
			+ "where ob1.broker_id =#{brokerid} and `status` >=8 AND ob1.ischecked=1) order_count "
			+ "FROM popuser WHERE	popid = #{brokerid} ")
	@Results({
		@Result(column="comment_count",property="commentCount"),
		@Result(column="isauthen",property="isAuthen"),
		@Result(column="order_count",property="orderCount")
		})
	public abstract BrokerIndexBean getInfo(int brokerid);
	
	/**
	 * 完成培训
	 * @param brokerid
	 * @return
	 */
	@Update ( value = "UPDATE popuser p SET istrained = 1 WHERE p.popid=#{brokerid}")
	public abstract int trained(int brokerid);
	
	/**
	 * 完成测试
	 * @param brokerid
	 * @return
	 */
	@Update ( value = "UPDATE popuser p SET istested = 1 WHERE p.popid=#{brokerid}")
	public abstract int tested(int brokerid);
	
	/**
	 * 认证经纪人
	 * @param broker
	 * @return
	 */
	@Update (value = "UPDATE popuser p SET p.real_name=#{realName},p.authen_tel=#{authenTel},p.id_num=#{idNum},"
			+ "p.hand_id_path=#{handIdPath},p.negative_id_path=#{negativeIdPath},p.positive_id_path=#{positiveIdPath},p.isauthen=1 "
			+ "WHERE p.popid = #{popid} AND p.istested =1 AND p.istrained=1")
	public abstract int authen(Broker broker);
	
	
	/**
	 * 培训内容的图片
	 * @return
	 */
	@Select(value = "SELECT tp.trainpicid,tp.pic_path,tp.`index`,tp.createtime,tp.updatetime FROM trainpic tp order by tp.`index`")
	@Results({
		@Result(column="trainpicid",property="trainPicId"),
		@Result(column="pic_path",property="picPath")
	})
	public abstract List<TrainPic> trainPics();
	
	/**
	 * 培训考试题目列表
	 * @return
	 */
	@Select(value= "SELECT tt.testid,tt.answer,tt.correct,tt.createtime,tt.updatetime,tt.`index` FROM traintest tt order by tt.`index`")
	public abstract List<TrainTest> trainTests();
	
	/**
	 * 认证信息详情
	 * @param brokerid
	 * @return
	 */
	@Select (value = "SELECT p.real_name,p.authen_tel,p.id_num,p.positive_id_path,p.negative_id_path,p.hand_id_path,p.istrained,p.istested,isauthen "
			+ "FROM popuser p WHERE popid=#{brokerid}")
	@Results({
		@Result(column="istrained",property="isTrained"),
		@Result(column="istested",property="isTested"),
		@Result(column="real_name",property="realName"),
		@Result(column="id_num",property="idNum"),
		@Result(column="isauthen",property="isAuthen"),
		@Result(column="authen_tel",property="authenTel"),
		@Result(column="positive_id_path",property="positiveIdPath"),
		@Result(column="negative_id_path",property="negativeIdPath"),
		@Result(column="hand_id_path",property="handIdPath")
	})
	public abstract Broker authenInfo(int brokerid);
	
	/**
	 * 经纪人投诉
	 * @param brokerid
	 * @param sys
	 * @param text
	 * @return
	 */
	@Insert(value="INSERT INTO feedback (text,createtime,system,popid) VALUES (#{text},#{createtime},#{system},#{brokerid});")
	public abstract int feedback(Feedback feedback);
	
	@Select("SELECT real_name FROM popuser WHERE popid=#{brokerid}")
	public abstract String queryBrokerUserName(int brokerid);
	
	@Select("SELECT isvAccountId FROM popuser WHERE popid=#{brokerid}")
	public abstract String queryBrokerIsvAccountId(int brokerid);
	
	
}
